import sys
import time

import pymysql

sys.path.extend(["F:/ML/BNC", "D:/BNC", "D:/develop/BNC", "/root/workplace/BNC"])
from DataBase.DatabaseConsts import MarketInfoDB, MarketTemperatureAutoTableName, MarketProfitAutoTableName, \
    MarketHighLimitAutoTableName, MySQLServerIP_199, MySQLServerIP_Slky_AliYun
from WebServer.blog.DB.DBConnection import DB_POOL
from DataBase.DatabaseConsts import CodeTagDateWeightTempTableName, StockOneDayKLineDB, \
    CodeTagDateTableName, StockBasicTableName, StockTagRenameTableName
from DataBase.DatabaseConsts import CodeTagDateWeightTempTableName, StockCalendarDB, \
    CodeTagDateTableName, StockBasicTableName, StockTagRenameTableName, AStockTagTable, MySQLServerIP_199
from Utils.Funcc import in_sql

MAX_SIZE = sys.maxsize
MIN_SIZE = -(MAX_SIZE - 100)


def bulking_market_temperature():
    """ 获取数据并刷新数据到远程连接库 """
    db = MarketInfoDB
    # 获取两个不同的DB连接
    local_199_db_pool = DB_POOL.get_db_pool(db, host=MySQLServerIP_199, port="3306", user="root", passwd="root",
                                            cursorclass=pymysql.cursors.Cursor)
    local_199_db_connection = local_199_db_pool.connection()
    local_199_db_cursor = local_199_db_connection.cursor()
    market_temperature_aliyun_db_pool = DB_POOL.get_remote_linux_db_pool(db,
                                                                         cursorclass=pymysql.cursors.Cursor)
    remote_linux_db_connection = market_temperature_aliyun_db_pool.connection()
    remote_linux_db_cursor = remote_linux_db_connection.cursor()

    table_s = [MarketTemperatureAutoTableName, MarketProfitAutoTableName, MarketHighLimitAutoTableName]
    try:
        # 使用增量进行更新
        for table_name_ in table_s:
            # 为null 返回-MAX_NUM + 1
            print("parse table : {} ".format(table_name_))

            select_lastest_two_day = "select distinct date from {} order by date desc limit 2".format(table_name_)
            local_199_db_cursor.execute(select_lastest_two_day)
            date_li = [r[0] for r in local_199_db_cursor.fetchall()]

            # 选取两天的数据
            select_local_sql = "select * from {} where date in {}".format(table_name_, in_sql(date_li))
            local_199_db_cursor.execute(select_local_sql)
            local_res = local_199_db_cursor.fetchall()

            # 删除之前的数据
            delete_remote_not_update_sql = "delete from {} where date in {}".format(table_name_, in_sql(date_li))
            remote_linux_db_cursor.execute(delete_remote_not_update_sql)
            remote_linux_db_connection.commit()

            insert_sql = "REPLACE INTO {} VALUES ({})".format(table_name_,
                                                              ("%s," * (len(local_res[0])))[:-1])
            remote_linux_db_cursor.executemany(insert_sql, local_res)
            remote_linux_db_connection.commit()
    finally:
        remote_linux_db_connection.close()
        local_199_db_connection.close()


def full_market_temperature():
    """ 获取数据并刷新数据到远程连接库 """
    db = MarketInfoDB
    # 获取两个不同的DB连接
    local_199_db_pool = DB_POOL.get_db_pool(db, host=MySQLServerIP_199, port="3306", user="root", passwd="root",
                                            cursorclass=pymysql.cursors.Cursor)
    local_199_db_connection = local_199_db_pool.connection()
    local_199_db_cursor = local_199_db_connection.cursor()
    market_temperature_aliyun_db_pool = DB_POOL.get_remote_linux_db_pool(db,
                                                                         cursorclass=pymysql.cursors.Cursor)
    remote_linux_db_connection = market_temperature_aliyun_db_pool.connection()
    remote_linux_db_cursor = remote_linux_db_connection.cursor()

    table_s = [MarketTemperatureAutoTableName, MarketProfitAutoTableName, MarketHighLimitAutoTableName]
    try:
        # 使用增量进行更新
        for table_name_ in table_s:
            # 为null 返回-MAX_NUM + 1
            print("parse table : {} ".format(table_name_))

            # 选取全部的数据
            select_local_sql = "select * from {} ;".format(table_name_)
            local_199_db_cursor.execute(select_local_sql)
            local_res = local_199_db_cursor.fetchall()

            # 删除之前的数据
            truncate_sql = "truncate {};".format(table_name_)
            remote_linux_db_cursor.execute(truncate_sql)
            remote_linux_db_connection.commit()

            insert_sql = "REPLACE INTO {} VALUES ({})".format(table_name_,
                                                              ("%s," * (len(local_res[0])))[:-1])
            remote_linux_db_cursor.executemany(insert_sql, local_res)
            remote_linux_db_connection.commit()
    finally:
        remote_linux_db_connection.close()
        local_199_db_connection.close()


def sync_calendar():
    """ 全量刷新需要的calendar表到远程 """
    db = StockOneDayKLineDB
    # 获取两个不同的DB连接
    local_199_db_pool = DB_POOL.get_db_pool(db, host="localhost", port="3306", user="root", passwd="h303567469",
                                            cursorclass=pymysql.cursors.Cursor)
    local_199_db_connection = local_199_db_pool.connection()
    local_199_db_cursor = local_199_db_connection.cursor()
    stock_calendar_aliyun_db_pool = DB_POOL.get_remote_linux_db_pool(db,
                                                                     cursorclass=pymysql.cursors.Cursor)
    remote_aliyun_db_connection = stock_calendar_aliyun_db_pool.connection()
    remote_aliyun_db_cursor = remote_aliyun_db_connection.cursor()

    # code_tag_date采用增量更新进行刷新，code_tag_date_weight_temp,stockbasic,stocktag,tag_rename使用增量
    table_s = [CodeTagDateWeightTempTableName, CodeTagDateTableName, StockBasicTableName, StockTagRenameTableName,
               AStockTagTable]
    bulking_table_s = [CodeTagDateTableName]

    try:
        # 使用增量进行更新
        for table_name_ in table_s:
            # 为null 返回-MAX_NUM + 1
            print("parse table : {} ".format(table_name_))
            if table_name_ in bulking_table_s:
                # 获取增量数据
                distinct_col_name = 'date'
                # 选取几日的数据
                default_time_len = 30
                # 默认更新最新的数据
                select_lastest_dates_sql = 'select %s from {} ORDER BY %s DESC limit {}'.format(table_name_,
                                                                                                default_time_len)
                local_199_db_cursor.execute(select_lastest_dates_sql, [distinct_col_name, distinct_col_name])
                update_date_s = [r[0] for r in local_199_db_cursor.fetchall()]

                select_lastest_data_sql = "select * from {} "
                pass
            else:
                # 选取全部的数据
                select_local_sql = "select * from {} ;".format(table_name_)
                local_199_db_cursor.execute(select_local_sql)
                local_res = local_199_db_cursor.fetchall()

                # 删除之前的数据
                truncate_sql = "truncate {};".format(table_name_)
                remote_aliyun_db_cursor.execute(truncate_sql)
                remote_aliyun_db_connection.commit()

                insert_sql = "REPLACE INTO {} VALUES ({})".format(table_name_,
                                                                  ("%s," * (len(local_res[0])))[:-1])
                remote_aliyun_db_cursor.executemany(insert_sql, local_res)
                remote_aliyun_db_connection.commit()

    finally:
        remote_aliyun_db_connection.close()
        local_199_db_connection.close()


if __name__ == '__main__':
    bulking_market_temperature()
    exit(0)
    arg_li = sys.argv
    allowed_method_li = ['full_market_temperature', 'bulking_market_temperature', 'full_calendar']
    method_ = allowed_method_li[0]
    if len(arg_li) > 1:
        method_ = arg_li[1]
    if method_ == 'full_market_temperature':
        full_market_temperature()
    elif method_ == 'bulking_market_temperature':
        bulking_market_temperature()
    elif method_ == "full_calendar":
        sync_calendar()
    else:
        print("miss catch args :{}".format(sys.argv))
